package com.feib.soeasy.dao.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.P0028MasterDao;
import com.feib.soeasy.model.P0028Master;
import com.feib.soeasy.model.Payment;

/**
 * @title (#)P0028MasterHibernateDao.java<br>
 * @description P0028Master Dao 實作<br>
 * @author Leo Lee<br>
 * @version 1.0.0 2011/12/08
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/12/08 create by Leo Lee<br>
 */
@Repository("p0028MasterDao")
public class P0028MasterHibernateDao extends BaseHibernateDao<P0028Master, Long> implements P0028MasterDao{

	@Override
	public List<P0028Master> queryP0028MasterByUsernos(List<String> usernos) {
		// TODO Auto-generated method stub
		Criteria criteria = getSession().createCriteria(P0028Master.class);
		criteria.add(Restrictions.in("userno", usernos));
		List<P0028Master> list = criteria.list();
		return list;
	}

	@Override
	public P0028Master getP0028MasterByUserno(String userno) {
		Criteria criteria = getSession().createCriteria(P0028Master.class);
		criteria.add(Restrictions.eq("userno", userno));
		List<P0028Master> list = criteria.list();
		if(list != null && list.size() >0)
			return list.get(0);
		return null;
	}
	
	@Override
	public List queryP0028MasterByAday(Date dateFrom , Date dateTo,String accountNo, Payment payment) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		String from = dateFormat.format(dateFrom);
		String to = dateFormat.format(dateTo);
		
		StringBuffer hql = new StringBuffer();
        hql.append("from P0028Master m ");
        hql.append("where substring(m.userno, 1, " + String.valueOf(accountNo.length()) + ") = :accountNo ");
        hql.append("and m.userno in (  ");
        hql.append("select (case length(payment.virtualAccount) when 14 then payment.virtualAccount else substring(payment.virtualAccount, 1, 14) end) as account  from Payment payment ");
        hql.append("	where payment.group = :group  ");
        hql.append("	and payment.uploadFile.formatDefinition = :formatDefinition ");
        
        if(payment.getPayerName() != null && !"".equals(payment.getPayerName()))
        {
        	hql.append(" and payment.payerName like :payerName ");
        }
        
        if(payment.getVirtualAccount() != null && !"".equals(payment.getVirtualAccount()))
        {
        	hql.append(" and payment.virtualAccount like :virtualAccount ");
        }
        if(payment.getPayerId() != null && !"".equals(payment.getPayerId()))
        {
        	hql.append(" and payment.payerId like :payerId ");
        }
        
        hql.append(") ");

        
        /*
        hql.append("where m.userno in (  ");
        hql.append("select d.userno from P0028Detail d where d.aday between :from and :to ");
        hql.append(") ");
        hql.append("and substring(m.userno, 1, " + String.valueOf(accountNo.length()) + ") = :accountNo ");
	*/
        Query query = getSession().createQuery(hql.toString());
        //query.setParameter("from",from);
        //query.setParameter("to",to);
        query.setParameter("accountNo",accountNo);
        query.setParameter("group", payment.getGroup());
        query.setParameter("formatDefinition", payment.getUploadFile().getFormatDefinition());
        if(payment.getPayerName() != null && !"".equals(payment.getPayerName()))
        {
        	query.setParameter("payerName", "%" + payment.getPayerName() + "%");
        }
        
        if(payment.getVirtualAccount() != null && !"".equals(payment.getVirtualAccount()))
        {
        	query.setParameter("virtualAccount", "%" + payment.getVirtualAccount() + "%");
        }
        
        if(payment.getPayerId() != null && !"".equals(payment.getPayerId()))
        {
        	query.setParameter("payerId", "%" + payment.getPayerId() + "%");
        }
        
		return query.list();
	}
	
	

}
